Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Making Selections and Working with Subsets of Data

In the OLAP DML, when you want to calculate against a subset of data, you can specify:

- The dimension values (and, therefore, what variable values) you want to have accessible or "in status" to all OLAP DML statements and expressions that execute against the data in a specific analytic workspace. For more information, see "Making a Subset of Data Available to All Statements Against a Workspace".

- The dimension values that you want Oracle OLAP to use when calculating an individual OLAP DML statement or expression. For more information, see "Making a Subset of Data Available to a Single Statement".

## Making a Subset of Data Available to All Statements Against a Workspace

Since data objects are multidimensional arrays in the OLAP DML, you can specify the subset of data that you want to work with by specifying the dimensionality of your arrays. You do this by specifying, for each dimension, which of its values are accessible or in the current status list of a dimension.

### Current Status Lists

The current status list of a dimension is an ordered list of currently accessible values for the dimension. Values that are in the current status list of a dimension are said to be "in status." The current status list of a dimension determines the selection of the data from all of the objects that are dimensioned by it.

For dimensions, only those dimension values that are in the current status list are accessed. For dimensioned objects, only those data values that are indexed by dimension values in the current status list are accessed. As a loop is performed through a dimensioned object, the order of the dimension values in the current status list is used to determine the order in which the values of the object are accessed.

Important: Whether or not a dimension value is in status merely restricts your view of the value during a given session; it does not permanently affect the values that are stored in the analytic workspace.

A dimension and any surrogate for that dimension share the same status. Setting the status of a dimension surrogate sets the status of its dimension and setting the status of a dimension sets the status of any dimension surrogates for it. In Part II, "Alphabetic Reference", references to dimensions apply equally to dimension surrogates, except where noted.

Composites are not dimensions, and therefore they do not have any independent status. The values of a composite that are "in status" are determined by the status of the base dimensions of the composite. In general, when statements deal with objects defined with composites, the default behavior is to treat those objects as if no SPARSE keyword or named composite had been used when the object was defined.

### Default Status Lists

When you first attach an analytic workspace, the current status list of each dimension consists of all of the values of the dimension that have read permission, in the order in which the values are stored. This list of values is called the default status list for the dimension.

You can change the default status list of a dimension in the following ways:

- You can add, delete, move, merge, and rename values in a dimension by using the MAINTAIN command or adding dimension values in other ways (for example, using a SQL FETCH statement).

- You can change the read permission of values that are associated with a dimension by using a PERMIT or PERMITRESET statement.

### Changing the Current Status List of a Dimension

Changing the default status list of a dimension obviously also changes the current status list of a dimension. Additionally, you can use to change the current status list of a dimension using:

- The LIMIT command which changes the values and the order of the values in the current status list of a dimension.

- The SORT command command which arranges the order of values in the current status list of a dimension.

The OLAP DML provides a number of statements that you can use to identify and retrieve the status of dimension values. These statements are listed in Table A-41, "Dimension and Composite Operation Statements".

### Saving and Restoring Current Dimension Status

There are several different ways that you can save the current status of a dimension. The scope of each way is different:

- Any session—To save the current status for use in any session, create a named valueset with that status. Use a DEFINE VALUESET statement to define the valueset. Use a LIMIT command to assign the values to the valueset.

- Current session—To save, access, or update the current status for use in the current session, then use a named context. Use the CONTEXT command to define the context.

- Current program—To save the current status for use in the current program, then use the PUSHLEVEL and PUSH statements. You can restore the current status values using the POPLEVEL and POP statements.

## Making a Subset of Data Available to a Single Statement

Sometimes you want to have an individual OLAP DML statement or expression work against a subset of data without actually changing the current status list of a dimension. To support this need, some OLAP DML statements allow you to specify the name of a previously-defined valueset object instead of the name of a dimension. Additionally, on-the-fly, you can specify the data subset that you want without changing the current status list of dimensions using the following functions:

- The CHGDIMS function which, during the evaluation of expression, changes the dimensionality of an expression or changes the dimension status.

- The LIMIT function which, during the evaluation of expression, returns the dimension or dimension surrogate values that result from a specified LIMIT command or a specified dimension status stack.